package com.api.gbhg.liuwenbo.version3.api;

import com.alibaba.fastjson.JSONObject;
import com.api.gbhg.liuwenbo.commons.entitis.CommonResult;
import com.api.gbhg.liuwenbo.commons.entitis.StatusMsg;
import com.api.gbhg.liuwenbo.commons.utils.CheckUtil;
import com.api.gbhg.liuwenbo.version3.pojo.RtExpensesPay;
import com.api.gbhg.luoao.la.util.R;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import weaver.general.GCONST;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.File;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @Description 报销细项支出导出
 * @Author lwb
 * @Date 2023.4.19
 */
@Path("v3/RtExpensesPayExport")
public class RtExpensesPayExportApi extends RtExpensesPayApi{

    private int thisIndex = 2;
    private HSSFCellStyle fontStyle = null;//正常字体大小靠右垂直居中
    private HSSFCellStyle fontCenterStyle = null;//字体大小跟居中
    private HSSFCellStyle cellStyle = null;//字体大小跟居中以及居中

    @POST
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String exportExcel(@Context HttpServletResponse response, Map param){
        if (!CheckUtil.isParamEmpty(param,checkKey))
            return new CommonResult<>(StatusMsg.CODE_400).toJSONString();

        List<RtExpensesPay> resultData = new LinkedList<>();
        try {
            if (param.get("srlx").toString().equals("0")){
                //财政拨款
                resultData.addAll(getZykmData(param));
            }else if(param.get("srlx").toString().equals("1")){
                //非财政拨款
                resultData.addAll(getFzyData(param));
            }else{
                //全部
                resultData.addAll(getZykmData(param));
                List<RtExpensesPay> fzyData = getFzyData(param);
                resultData.addAll(getFzyData(param));
            }

            String s = downLoadExcel(response, resultData);

        } catch (Exception e) {
            e.printStackTrace();
            return new CommonResult<>(500,e.toString()).toJSONString();
        }

        return new CommonResult<>(StatusMsg.CODE_200).toJSONString();
    }

    private String downLoadExcel(HttpServletResponse response,List<RtExpensesPay> data)  throws Exception{

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("报销细项支出");


        //新增数据行，并且设置单元格数据
        HSSFRow row = sheet.createRow(0);
        //定义单元格风格
        cellStyle = workbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)11);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);

        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        //设置行的高度
        cellStyle.setFont(font);
        row.setHeightInPoints(20);

        sheet.setColumnWidth(0,1000*10);
        sheet.setColumnWidth(1,600*10);
        sheet.setColumnWidth(2,1000*10);
        sheet.setColumnWidth(3,600*10);
        sheet.setColumnWidth(4,600*10);
        sheet.setColumnWidth(5,600*10);
        sheet.setColumnWidth(6,600*10);

        //设置背景颜色的style
        HSSFCellStyle coloTitleStyle = workbook.createCellStyle();
        coloTitleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        coloTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        coloTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字段style
        HSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short)11);
        coloTitleStyle.setFont(fontTitle);

        //创建字体大小无居中style----------------
        fontStyle = workbook.createCellStyle();
        fontStyle.setFont(fontTitle);
        fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //创建字体大小水平居中style----------------
        fontCenterStyle = workbook.createCellStyle();
        fontCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        fontCenterStyle.setAlignment(HorizontalAlignment.CENTER);
        fontCenterStyle.setFont(fontTitle);

        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
        sheet.addMergedRegion(region);
        //添加excel表头第一行
        HSSFCell cellTl = row.createCell(0);
        cellTl.setCellStyle(cellStyle);
        HSSFRichTextString title = new HSSFRichTextString("报销细项支出");
        cellTl.setCellValue(title);

        //--------设置列标题----------------------------------
        String[] headers = {"收入預算","使用单位","流程编号","部门预算经济分类","政府预算经济分类","报销金额","审核日期"};

        HSSFRow rowbt2 = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = rowbt2.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }
        //------------开始写入数据
        int startIx0 = thisIndex;//此下标作为起始下标用 用作于求和起始位置 以及合并行起始位置
        int startIx1 = thisIndex;//此下标作为起始下标用 用作于求和起始位置 以及合并行起始位置

        for (int i=0;i<data.size();i++){
            RtExpensesPay bean = data.get(i);

            HSSFRow dataRow = sheet.createRow(thisIndex);
            HSSFCell ysfl = dataRow.createCell(0);
            ysfl.setCellValue(bean.getName());
            ysfl.setCellStyle(fontStyle);

            List<RtExpensesPay> xj1 = bean.getXj();
            if (xj1.size()>0){//单位层
                for (int j = 0; j < xj1.size(); j++) {
                    RtExpensesPay xjBean1 = xj1.get(j);

                    List<RtExpensesPay> xj2 = xjBean1.getXj();
                    if (xj2.size()>0){//最终数据层
                        for (int k = 0; k < xj2.size(); k++) {
                            RtExpensesPay xjBean2 = xj2.get(k);
                            HSSFRow mxRow ;
                            if (startIx0==thisIndex){
                                mxRow = sheet.getRow(thisIndex);
                            }else{
                                mxRow = sheet.createRow(thisIndex);
                            }

                            HSSFCell cell2 = mxRow.createCell(2);
                            cell2.setCellValue(xjBean2.getLcmc());
                            cell2.setCellStyle(fontStyle);

                            HSSFCell cell3 = mxRow.createCell(3);
                            cell3.setCellValue(xjBean2.getBmjjfl());
                            cell3.setCellStyle(fontStyle);

                            HSSFCell cell4 = mxRow.createCell(4);
                            cell4.setCellValue(xjBean2.getZfjjfl());
                            cell4.setCellStyle(fontStyle);

                            HSSFCell cell5 = mxRow.createCell(5);
                            cell5.setCellValue(xjBean2.getBxje());
                            cell5.setCellStyle(fontStyle);

                            HSSFCell cell6 = mxRow.createCell(6);
                            cell6.setCellValue(xjBean2.getShrq());
                            cell6.setCellStyle(fontStyle);

                            thisIndex++;
                        }
                        if (startIx1<thisIndex-1){
                            CellRangeAddress temp = new CellRangeAddress(startIx1, thisIndex-1, 1, 1);
                            sheet.addMergedRegion(temp);
                        }

                    }
                    HSSFRow dwRow = sheet.getRow(startIx1);
                    HSSFCell sydw = dwRow.createCell(1);
                    sydw.setCellValue(xjBean1.getDwmc());
                    sydw.setCellStyle(fontStyle);

                    startIx1 = thisIndex;

                }
                if (startIx0<thisIndex-1) {
                    CellRangeAddress temp = new CellRangeAddress(startIx0, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(temp);
                }
            }
            //坐标归位
            startIx0 = thisIndex;
        }


        //设置要导出的文件的名字
        String downFilename  = "报销细项支出"  + ".xls";
        //服务器发给浏览器的数据是使用UTF-8编码
        response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");

        //设置浏览器的编码为UTF-8
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(downFilename,"UTF-8"));

        response.flushBuffer();
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();

        return "success";
    }


    private void downLoadExcel(List<RtExpensesPay> data)  throws Exception{

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("报销细项支出");


        //新增数据行，并且设置单元格数据
        HSSFRow row = sheet.createRow(0);
        //定义单元格风格
        cellStyle = workbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)11);
        //设置是否为斜体
        font.setItalic(false);
        //设置是否加粗
        font.setBold(true);

        //设置字体颜色
        font.setColor(IndexedColors.BLACK.index);
        //设置行的高度
        cellStyle.setFont(font);
        row.setHeightInPoints(20);

        sheet.setColumnWidth(0,1000*10);
        sheet.setColumnWidth(1,600*10);
        sheet.setColumnWidth(2,1000*10);
        sheet.setColumnWidth(3,600*10);
        sheet.setColumnWidth(4,600*10);
        sheet.setColumnWidth(5,600*10);
        sheet.setColumnWidth(6,600*10);

        //设置背景颜色的style
        HSSFCellStyle coloTitleStyle = workbook.createCellStyle();
        coloTitleStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        coloTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        coloTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字段style
        HSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short)11);
        coloTitleStyle.setFont(fontTitle);

        //创建字体大小无居中style----------------
        fontStyle = workbook.createCellStyle();
        fontStyle.setFont(fontTitle);
        fontStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //创建字体大小水平居中style----------------
        fontCenterStyle = workbook.createCellStyle();
        fontCenterStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        fontCenterStyle.setAlignment(HorizontalAlignment.CENTER);
        fontCenterStyle.setFont(fontTitle);

        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
        sheet.addMergedRegion(region);
        //添加excel表头第一行
        HSSFCell cellTl = row.createCell(0);
        cellTl.setCellStyle(cellStyle);
        HSSFRichTextString title = new HSSFRichTextString("报销细项支出");
        cellTl.setCellValue(title);

        //--------设置列标题----------------------------------
        String[] headers = {"收入預算","使用单位","流程编号","部门预算经济分类","政府预算经济分类","报销金额","审核日期"};

        HSSFRow rowbt2 = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = rowbt2.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(cellStyle);
        }
        //------------开始写入数据
        int startIx0 = thisIndex;//此下标作为起始下标用 用作于求和起始位置 以及合并行起始位置
        int startIx1 = thisIndex;//此下标作为起始下标用 用作于求和起始位置 以及合并行起始位置

        for (int i=0;i<data.size();i++){
            RtExpensesPay bean = data.get(i);

            HSSFRow dataRow = sheet.createRow(thisIndex);
            HSSFCell ysfl = dataRow.createCell(0);
            ysfl.setCellValue(bean.getName());
            ysfl.setCellStyle(fontStyle);

            List<RtExpensesPay> xj1 = bean.getXj();
            if (xj1.size()>0){//单位层
                for (int j = 0; j < xj1.size(); j++) {
                    RtExpensesPay xjBean1 = xj1.get(j);

                    List<RtExpensesPay> xj2 = xjBean1.getXj();
                    if (xj2.size()>0){//最终数据层
                        for (int k = 0; k < xj2.size(); k++) {
                            RtExpensesPay xjBean2 = xj2.get(k);
                            HSSFRow mxRow ;
                            if (startIx0==thisIndex){
                                mxRow = sheet.getRow(thisIndex);
                            }else{
                                mxRow = sheet.createRow(thisIndex);
                            }

                            HSSFCell cell2 = mxRow.createCell(2);
                            cell2.setCellValue(xjBean2.getLcmc());
                            cell2.setCellStyle(fontStyle);

                            HSSFCell cell3 = mxRow.createCell(3);
                            cell3.setCellValue(xjBean2.getBmjjfl());
                            cell3.setCellStyle(fontStyle);

                            HSSFCell cell4 = mxRow.createCell(4);
                            cell4.setCellValue(xjBean2.getZfjjfl());
                            cell4.setCellStyle(fontStyle);

                            HSSFCell cell5 = mxRow.createCell(5);
                            cell5.setCellValue(xjBean2.getBxje());
                            cell5.setCellStyle(fontStyle);

                            HSSFCell cell6 = mxRow.createCell(6);
                            cell6.setCellValue(xjBean2.getShrq());
                            cell6.setCellStyle(fontStyle);

                            thisIndex++;
                        }
                        if (startIx1<thisIndex-1){
                            CellRangeAddress temp = new CellRangeAddress(startIx1, thisIndex-1, 1, 1);
                            sheet.addMergedRegion(temp);
                        }

                    }
                    HSSFRow dwRow = sheet.getRow(startIx1);
                    HSSFCell sydw = dwRow.createCell(1);
                    sydw.setCellValue(xjBean1.getDwmc());
                    sydw.setCellStyle(fontStyle);

                    startIx1 = thisIndex;

                }
                if (startIx0<thisIndex-1) {
                    CellRangeAddress temp = new CellRangeAddress(startIx0, thisIndex-1, 0, 0);
                    sheet.addMergedRegion(temp);
                }
            }
            //坐标归位
            startIx0 = thisIndex;
        }


        //设置要导出的文件的名字
        String downFilename  = "报销细项支出"  + ".xls";
        String rootPath = GCONST.getRootPath()+"log/gbhg/huangwm/";
        String path = rootPath + downFilename;
        File file = new File(path);
        workbook.write(file);
    }

    public void exportExcel( Map param){
        if (!CheckUtil.isParamEmpty(param,checkKey))
            System.err.println("params error");

        List<RtExpensesPay> resultData = new LinkedList<>();
        try {
            if (param.get("srlx").toString().equals("0")){
                //财政拨款
                resultData.addAll(getZykmData(param));
            }else if(param.get("srlx").toString().equals("1")){
                //非财政拨款
                resultData.addAll(getFzyData(param));
            }else{
                //全部
                resultData.addAll(getZykmData(param));
                List<RtExpensesPay> fzyData = getFzyData(param);
                resultData.addAll(getFzyData(param));
            }

             downLoadExcel(resultData);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        String rootPath = "D:\\WEAVER\\ecology\\";
        GCONST.setRootPath(rootPath);
        GCONST.setServerName("ecology");

        Map params = new HashMap();
        params.put("bmysjjfl","");
        params.put("dfczbkxm","");
        params.put("fzysrfl","");
        params.put("zfysjjfl","");
        params.put("dygnkmbid","");
        params.put("srlx","");
        params.put("ysly","");
        params.put("jbbm","");
        params.put("year","2023");
        params.put("pageNumber","1");
        params.put("pageSize","30");
        System.err.println(JSONObject.toJSONString(params));
        //RtExpensesPayExportApi api = new RtExpensesPayExportApi();
        //api.exportExcel(params);
    }
}
